엑셀 INDIRECT 함수는 텍스트로 작성된 셀 주소를 직접 참조하여 해당 셀의 값을 반환하는 함수입니다.
=INDIRECT(참조범위, [참조방식])
- 다른 시트를 참조할 경우, "'시트명'!A1" 형식으로 작성합니다.
- 다른 통합문서를 참조할 경우, '[통합문서1]Sheet1'!A1" 형식으로 작성합니다.
- TRUE : [기본값] A1 스타일
- FALSE : R1C1 스타일
엑셀 INDIRECT 함수는 텍스트로 작성된 셀이나 범위 주소를 직접 참조하여 해당 셀이나 범위의 값을 반환하는 함수입니다. 이 함수를 사용하면 셀이나 범위를 동적으로 참조할 수 있어 유연한 수식 작성이 가능합니다.
=INDIRECT("A1") / / A1셀의 값을 반환합니다. =INDIRECT("A" & DAY(TODAY())) / / 오늘 날짜의 일에 따라 A1 ~ A31 까지 범위를 참조합니다.
특히 다른 함수나 셀의 값을 기반으로 동적으로 범위를 참조하는 자동화 함수를 작성할 때 유용하게 사용할 수 있습니다.
=INDIRECT("'" & A1 & "'!C1:C100") / / A1셀에 작성된 시트의 C1:C100 범위를 참조합니다. =SUM(INDIRECT("'" & A1 & "'!C1:C100")) / / A1셀에 작성된 시트에서 C1:C100 범위의 합계를 구합니다.
만약 다른 통합문서를 참조할 경우, INDIRECT 함수는 다음과 같이 작성합니다.
= INDIRECT("'[통합문서1]Sheet1'!"&"A1")
다음과 같이 수식을 작성하면, A1셀에 작성된 셀 주소의 값을 동적으로 참조합니다.
=INDIRECT(A1) / / A1셀이 "B5"일 경우 B5셀의 값을 반환합니다.
다음과 같이 수식을 작성하면, A1셀에 작성한 시트의 특정 범위를 동적으로 참조할 수 있습니다.
=INDIRECT("'" & A1 & "'!B1") / / A1셀에 "1월"이 입력된 경우, 1월 시트의 B1셀을 참조합니다.
다음과 같이 VLOOKUP 함수와 INDIRECT 함수를 함께 사용해서, 여러 시트의 데이터를 동적으로 검색하는 공식을 작성할 수 있습니다.
=VLOOKUP("김하늘",INDIRECT("'" & A1 & "'!A:C"),2,0) / / A1셀에 "2월"이 입력된 경우, '김하늘' 직원의 2월 매출을 검색합니다. / / A1셀에 "4월"이 입력된 경우, '김하늘' 직원의 4월 매출을 검색합니다.
INDIRECT 함수 + VLOOKUP 함수 공식의 자세한 동작 원리는 아래 기초 입문 강의에서 꼼꼼히 정리했으니 참고하세요!👇
다음과 같이 INDIRECT 함수를 작성하면 다른 통합문서의 데이터를 동적으로 참조할 수 있습니다. 단, 참조하려는 통합문서는 반드시 열려있어야 합니다.
=INDIRECT("'[예산.xlsx]Sheet1'!A1") / / 예산이라는 통합문서에서 Sheet1 시트의 A1셀을 참조합니다.
INDIRECT 함수는 참조하는 셀의 값만 반환하며 서식은 반환하지 않습니다. 예를 들어, 참조하는 셀이 통화서식($4.00)으로 작성되어 있더라도, INDIRECT 함수는 '4'를 반환합니다.
참조할 범위가 유효하지 않을 경우, INDIRECT 함수는 #REF! 오류를 반환합니다.
INDIRECT 함수로 다른 통합문서나 시트를 참조할 경우, 통합문서는 대괄호("[]"), 시트는 느낌표("!")로 구분하여 입력합니다.
=INDIRECT("[직원정보.xlsx]"&"Sheet1!"&"A1") / / 직원정보 파일에서 Sheet1 시트의 A1셀을 참조합니다.
INDIRECT 함수의 기본 참조방식은 A1 방식입니다. 만약 R1C1 방식으로 셀을 참조할 경우 두번째 인수인 참조방식을 FALSE로 입력하세요.
그런데 혹시
=VLOOKUP(E5,INDIRECT("D:\work\2020_1분기\정보센터\통계\[5월_현황(일반현황).xls]"&"sheet1'!"&"$A$7:$R$1324"),2,FALSE)
=VLOOKUP(E5,INDIRECT("[5월_현황(일반현황).xls]"&"sheet1'!"&"$A$7:$R$1324"),2,FALSE)
이 두 식을 사용하면 둘 다 해당 엑셀 통합 문서를 열어놨음에도 불구하고 #REF! 오류가 뜹니다. 왜 그런지 이유를 모르겠습니다...
그리고 다른 통합 문서의 시트를 참고할 때는 전자처럼 주소를 써줘야 하는지, 아닌지도 궁금합니다. 혹시 몰라서 같은 폴더에도 넣어보고 해당 폴더(D:\work\2020_1분기\정보센터\통계)에도 넣어봤지만 둘 다 참조를 못해서요.. 혹시 방법이 잘못 되었을까요?
=VLOOKUP(E5,INDIRECT("[5월_현황(일반현황).xls]"&"sheet1'!"&"$A$7:$R$1324"),2,FALSE)
Sheet1과 ! 사이에 [ ' ] 보이시나요?
' 가 ! 전에 추가되었으므로, 파일명 맨 앞에도 [ ' ] 를 추가해주셔야 합니다.
'(홑따옴표)만 추가하시면 바로 해결될겁니다.^^
제가 월별로 관리하는 시트가 있는데, 2월을 예로 들면, 시트는 일자별(0201 ~ 0228)까지 되어 있고, 월간 통계를 위해서 하나의 시트가 추가 되는 형식입니다. 입력되는 양식이 일정하다보니, 월간 통계를 위해서 =SUM('0201:0228'!AB5) 이런 식으로 합계를 구하고 있습니다. 그런데, 시트에서 합계를 구하는 부분이 많다보니 매월 새로운 시트의 수식을 변경을하기가 어려워서 INDIRECT 함수를 사용해서 변경을 하려고 합니다. 그런데, 이게 다중셀 계산이라서 그런지, 제가 할줄 모르는건지... 계속 에러(#REF!)가 뜨네요. 혹시 해결 방안이 있을까요?
INDIRECT 함수로 범위를 올바르게 참조하였는지 다시 확인해보세요.
수식은 아래와 같이 입력하시면 됩니다.
=SUM(INDIRECT("'0201:0228'!AB5"))
또는 아래 커뮤니티 게시판을 통해 질문을 올려주시면 다른 여러 엑셀 전문가분께 답변을 받으실 수 있습니다.제공해주신 파일의 Sheet1!D12의 내용과 E12의 실제 입력된 함수가 다릅니다.
제가 보기엔 D12가 오타인것 같습니다.
말씀해주신 내용 수정하였습니다.
확인해주셔서 감사드립니다.
INDIRECT 함수는 현재 실행중인 파일만 참조할 수 있어요.
만약 닫혀져있는 파일을 참조하려면, INDIRECT 함수대신 =X:\공부\엑셀공부\상위폴더\하위폴더\최하위폴더\[연습.xlsx]테스트!B2
이런 식으로 값을 직접 참조해주셔야 합니다.
저 궁금한 점이 있습니다.ㅠㅠ
파일명에 띄어쓰기가 들어가면 저 수식이 오류로 뜨는데,
작은따음표를 넣어도 안되어서요..ㅠㅠ 혹시 원인이 무엇인지
도움받을수있을까요?
수식을 아래처럼 바꿔보시겠어요?
INDIRECT("'["&TEXT($B1,)&".xls]"&B2&"'!B7")
홑따옴표(') 를 양 끝으로 넣어주시면 됩니다.='C:\Users\user\Desktop\[4분기.xlsx]&text(a5,"MM-DD")&"'!O34"
이렇게 다른 파일의 시트의 셀값을 가져오려하는데
"워크시트 수식에 일부 잘못된 참조가 있습니다" 라고 뜹니다.
따옴표와 큰따옴표의 위치를 못잡겠어요 ㅠㅠ 부디 도와주세요
수식을 아래처럼 수정해보세요.
수식을 아래와 같이 수정해보시겠어요?